

import com.wqn.JDBCUtils;
import com.wqn.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class Main {

    private static Connection con = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    private static Scanner scan = new Scanner(System.in);
    private static Boolean isExit = false;


    private static String studentId;
    private static String sname;
    private static int sage;
    private static String ssex;
    private static String deptId;
    private static String sClass;




    public static void main(String[] args) {
        System.out.println("欢迎来到学生管理系统\n");

        while (!isExit){
            // 打印菜单
            printMenu();
            // 获取命令执行操作
            getCommend();
        }

    }





    // 打印菜单
    public static void printMenu(){

        System.out.println(
                "请输入相应的命令菜单:\n" +
                        "1 添加数据；\n" +
                        "2 查询数据；\n" +
                        "3 修改数据；\n" +
                        "4 删除数据；\n" +
                        "5 退出系统；");
    }

    // 获取学生属性
    public static  void getStuPar(){
        System.out.println("请输入学号:");
        studentId = scan.next();
        System.out.println("请输入姓名:");
        sname = scan.next();
        System.out.println("请输入年龄");
        sage = scan.nextInt();
        System.out.println("请输入性别:");
        ssex = scan.next();
        System.out.println("请输入部门id:");
        deptId = scan.next();
        System.out.println("请输入班级id:");
        sClass = scan.next();
    }
    // 获取命令编号执行对应操作
    public static void getCommend(){

        System.out.println("请输入命令编号:");
        int s = scan.nextInt();
        switch (s){
            case 1:
                getStuPar();
                insertStu(new Student(studentId,sname,sage,ssex,deptId,sClass));
                break;
            case 2:
                searchStu();
                break;
            case 3:
                getStuPar();
                updateStu(new Student(studentId,sname,sage,ssex,deptId,sClass));
                break;
            case 4:
                deleteStu();
                break;
            case 5:
                isExit = true;
                System.out.println("退出系统成功");

                break;
            default:
                System.out.println("输入错误，请重新输入！");
                getCommend();
                break;
        }

    }

    // 插入学生
    public static void insertStu(Student student){
        try{
            //获取链接
            con = JDBCUtils.getConnection();

            String sql = "insert into student values(?,?,?,?,?,?)";
// 向方法prepareStatement中传入参数sql,   prepareStatement 对sql 进行处理
            ps =  con.prepareStatement(sql);

            ps.setString(1,student.getStudent_id());
            ps.setString(2,student.getSname());
            ps.setInt(3,student.getSage());
            ps.setString(4,student.getSsex());
            ps.setString(5,student.getDept_id());
            ps.setString(6,student.getSclass());

            if (ps.executeUpdate()>0){
                System.out.println("插入成功");
            }else {
                System.out.println("插入失败");
            }

        }catch (Exception e){
            e.printStackTrace();
        }
        JDBCUtils.close(rs,ps,con);
    }

    // 删除学生
    private static void deleteStu() {
        try{
            con = JDBCUtils.getConnection();
            //使用prepareStatement方法，对sql字段进行预处理，从student表里删除，如果学生id等于sql字段则进行删除
            String sql = "delete  from student where Student_id = ?";
            ps =  con.prepareStatement(sql);
            System.out.println("请输入学号:");
            String s = new Scanner(System.in).next();
            ps.setString(1,s);
            if (ps.executeUpdate()>0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        JDBCUtils.close(rs,ps,con);
    }

    // 修改学生信息
    private static void updateStu(Student student) {
        try{
            con = JDBCUtils.getConnection();
            //使用prepareStatement方法，对sql字段进行预处理，更新学生表指定id同学的姓名。。。
            String sql = "update student set Sname =?, Sage = ?, Ssex =?, Dept_id = ?,Sclass = ?   where Student_id = ?";
            ps =  con.prepareStatement(sql);
            ps.setString(1,student.getSname());
            ps.setInt(2,student.getSage());
            ps.setString(3,student.getSsex());
            ps.setString(4,student.getDept_id());
            ps.setString(5,student.getSclass());
            ps.setString(6,student.getStudent_id());
            System.out.println(ps.executeUpdate());
            if(ps.executeUpdate()>0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }

        }catch (Exception e){
            e.printStackTrace();
        }
        JDBCUtils.close(rs,ps,con);
    }

    // 查询学生
    public static void  searchStu(){
        try{
            con = JDBCUtils.getConnection();
            //* 表示student表里的所有字段
            // //使用prepareStatement方法，对sql字段进行预处理，从学生表里进行学生的查询
            String sql = "SELECT * from student ";
            ps =  con.prepareStatement(sql);
            rs = ps.executeQuery();
            System.out.println();
            while (rs.next()) {
                System.out.println(
                        "\t学号:"+ rs.getString("Student_id")+
                                "\t姓名:" + rs.getString("Sname")+
                                "\t年龄:" + rs.getString("Sage")+
                                "\t性别:" + rs.getString("Ssex")+
                                "\t部门id:" + rs.getString("Dept_id")+
                                "\t班级id:" + rs.getString("Sclass")

                );
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        JDBCUtils.close(rs,ps,con);
    }

}
